package cn.edu.lingnan.dao;

import cn.edu.lingnan.pojo.Competition;
import cn.edu.lingnan.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ICompetitionDao implements CompetitionDao{
    @Override
    public List<Competition> findCompetitionByName(String name) {
        List<Competition> competitionList = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getInstance().getConnection();
            ps = conn.prepareStatement("select * from competitions where name like concat('%',?,'%')");
            ps.setString(1,name);
            rs=ps.executeQuery();
            while(rs.next()){
                Competition competition = new Competition();
                competition.setId(rs.getInt("id"));
                competition.setName(rs.getString("name"));
                competition.setDescription(rs.getString("description"));
                competition.setStart_time(rs.getString("start_time"));
                competition.setEnd_time(rs.getString("end_time"));
                competitionList.add(competition);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        finally {
            DBUtil.getInstance().close(rs,ps,conn);
        }
        return competitionList;
    }

    @Override
    public List<Competition> findCompetitionAll() {
        List<Competition> competitions = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getInstance().getConnection();
            ps = conn.prepareStatement("SELECT * FROM competitions");
            rs=ps.executeQuery();
            while (rs.next()) {
                Competition competition = new Competition();
                competition.setId(rs.getInt("id"));
                competition.setName(rs.getString("name"));
                competition.setDescription(rs.getString("description"));
                competition.setStart_time(rs.getString("start_time"));
                competition.setEnd_time(rs.getString("end_time"));
                competitions.add(competition);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        finally {
            DBUtil.getInstance().close(rs,ps,conn);
        }
        return competitions;
    }

    @Override
    public int addCompetition(Competition competition) {
        int flag = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBUtil.getInstance().getConnection();
            ps = conn.prepareStatement("INSERT INTO competitions (name, description, start_time, end_time) VALUES (?, ?, ?, ?)");
                ps.setString(1, competition.getName());
                ps.setString(2, competition.getDescription());
                ps.setString(3, competition.getStart_time());
                ps.setString(4, competition.getEnd_time());
                ps.executeUpdate();
                flag=1;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        finally {
            DBUtil.getInstance().close(null,ps,conn);
        }
        return flag;
    }

    @Override
    public int delCompetitionByName(String name) {
        PreparedStatement stmt = null;
        try (Connection conn = DBUtil.getInstance().getConnection()) {
            stmt = conn.prepareStatement("DELETE FROM competitions WHERE name = ?");
            stmt.setString(1, name);
            int rowsAffected = stmt.executeUpdate();
            return rowsAffected > 0 ? 1 : 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        } finally {//stmt还没关闭故要调用close();
            DBUtil.getInstance().close(null, stmt, null);
        }
    }

    @Override
    public int delCompetitionById(int id) {
            int flag = 0;
            try (Connection conn = DBUtil.getInstance().getConnection();
                 PreparedStatement ps = conn.prepareStatement("delete from competitions where id=?")) {
                ps.setInt(1, id);
                int rowsAffected = ps.executeUpdate();
                if (rowsAffected > 0) {
                    flag = 1; // 删除成功
                } else {
                    flag = 0; // 删除失败
                }
            } catch (SQLException e) {
                return 2;//发生异常或竞赛记录表中有记录无法进行删除
            }
            return flag;
    }

    @Override
    public int updateCompetitionById(Competition competition) {
        int flag = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getInstance().getConnection();
                ps = conn.prepareStatement("update competitions set name=?,description=?,start_time=?,end_time=? where id=?");
                ps.setString(1, competition.getName());
                ps.setString(2, competition.getDescription());
                ps.setString(3, competition.getStart_time());
                ps.setString(4, competition.getEnd_time());
                ps.setInt(5, competition.getId());
                int rowsAffected = ps.executeUpdate();
                if (rowsAffected > 0) {
                    flag = 1; // 删除成功
                } else {
                    flag = 0; // 删除失败
                }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.getInstance().close(null, ps, conn);
        }
        return flag;
        }

    @Override
    public boolean isCompeteRegPart(int competitionId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean result = false;

        try {
            conn = DBUtil.getInstance().getConnection();
            String sql = "SELECT * FROM participation WHERE competition_id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, competitionId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                result = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            DBUtil.getInstance().close(rs, pstmt, conn);
            }
        return result;
    }

    @Override
    public List<String> projectList() {
        List<String> projectList = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getInstance().getConnection();
            ps = conn.prepareStatement("SELECT name FROM competitions;");
            rs = ps.executeQuery();
            while (rs.next()) {
                String projectName = rs.getString("name");
                projectList.add(projectName);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.getInstance().close(rs, ps, conn);
        }
        return projectList;
    }

}
